use Att2000
GO
select * from USERINFO;

select * from att_person;


--按校区分主管申诉
create table att_persons
(
	per_id int identity(1,1) primary key not null,
	per_sch_id int ,--对应的DEPTID
	per_user_id int,   --USERID
	per_title nvarchar(30), --TITLE
	per_user_name nvarchar(30), --NAME
	per_status int -- 状态
)
drop table att_persons;

-----------------------------------------1级------------------
insert into att_persons values(0,555,'深圳中','赵麟',1);
insert into att_persons values(0,531,'深圳东','杨小改',1);
-----------------------------------------2级片区---------------------
insert into att_persons values(1,473,'百花、园东','沈倩',2);
insert into att_persons values(2,414,'莲花北、村','黄丽纯',2);
insert into att_persons values(3,1503,'石厦、益田','陈思',2);
insert into att_persons values(4,1369,'景田、香蜜湖','王杨',2);
insert into att_persons values(5,420,'罗湖、莲沙','黄奕嫦',2);
insert into att_persons values(6,1513,'宝安、前海','董鑫信',2);
insert into att_persons values(7,361,'后海','毕玉',2);
----------------------------------------3级各校区主管---------------
--1百花/英语主管
insert into att_persons values(1,369,'英语(1`4)','陈欢欢',3);
insert into att_persons values(1,473,'英语(5`7)','沈倩',3);
insert into att_persons values(1,489,'英语(8`9)','王灿',3);
--校区主管
insert into att_persons values(1,488,'园岭','汪小燕',3);
insert into att_persons values(1,398,'园东','冮静',3);
insert into att_persons values(1,527,'长安','杨慧',3);
insert into att_persons values(1,533,'白沙岭','衣雪',3);

----2莲花村/北
insert into att_persons values(2,268,'英语(1`4)','张芳芳',3);
insert into att_persons values(2,414,'英语(5`7)','黄丽纯',3);
insert into att_persons values(2,277,'英语(8`9)','曾莹',3);
--校区主管
insert into att_persons values(2,285,'莲花北','何芳瑛',3);
insert into att_persons values(2,513,'莲花村','肖辉',3);


----3石厦/益田
insert into att_persons values(3,438,'英语(1`4)','李雅',3);
insert into att_persons values(3,438,'英语(5`7)','李雅',3);
insert into att_persons values(3,1503,'英语(8`9)','陈思',3);
--校区主管
insert into att_persons values(3,566,'石厦','周若真',3);
insert into att_persons values(3,566,'益田','周若真',3);

----4景田/香蜜湖
insert into att_persons values(4,516,'英语(1`4)','谢明婷',3);
insert into att_persons values(4,1369,'英语(5`7)','王杨',3);
insert into att_persons values(4,1369,'英语(8`9)','王杨',3);
--校区主管
insert into att_persons values(4,210,'景田','张宝娣',3);
insert into att_persons values(4,462,'香蜜湖','聂丹',3);


----5罗湖/沙头角/莲塘
insert into att_persons values(5,480,'英语(1`4)','谭浪',3);
insert into att_persons values(5,420,'英语(5`7)','黄奕嫦',3);
insert into att_persons values(5,402,'英语(8`9)','郭海晓',3);
insert into att_persons values(5,220,'英语(莲沙)','龚雪飞',3);
insert into att_persons values(5,389,'数学','邓玲',3);
--校区主管
insert into att_persons values(5,387,'罗湖','储成佳',3);
insert into att_persons values(5,477,'莲塘','孙梦婕',3);
insert into att_persons values(5,450,'沙头角','刘婷婷',3);


--6宝安前海
insert into att_persons values(6,1513,'宝安、前海','董鑫信',3);
--7后海
insert into att_persons values(7,361,'后海','毕玉',3);

--8职能中心
insert into att_persons values(8,1489,'财务部','张薇',3);
insert into att_persons values(8,544,'企划部','张赫越',3);
insert into att_persons values(8,564,'信息技术部','周敏',3);
insert into att_persons values(8,1028,'行政中心','周斌',3);
insert into att_persons values(8,431,'英语','黎小白',3);


--数学、语文、科学（各校区共有）
insert into att_persons values(10,1028,'数学','周斌',3);
insert into att_persons values(10,291,'语文','胡欣',3);
insert into att_persons values(10,1030,'科学','杨姐',3);
insert into att_persons values(10,1024,'数学中学部','翟雄超',3);



--查询ID
select * from USERINFO where Name like '%操海棠%';

select * from att_persons;
--
select u.tea_id,p.per_title,p.per_user_name 
from att_persons p,USERINFO u 
where u.USERID=p.per_user_id 
and per_sch_id in (8,10) and per_status=3;

update att_persons set per_sch_id =7 where per_id=40 ;

select * from DEPARTMENTS;

alter table Departments add att_type int default 0;
update DEPARTMENTS set att_type=8 where DEPTID in (16);

select u.Name,a.* 
from Attendance a,USERINFO u 
where a.att_user_id=u.USERID and u.Name like '%江%'
order by att_date desc

update Attendance set att_confirm=5 where att_id in (158051,158579,159323);

select u.Name,u.tea_id,d.att_type 
from USERINFO u ,DEPARTMENTS d 
where u.DEFAULTDEPTID=d.DEPTID 
and u.USERID=2;


select u.tea_id,p.per_title,p.per_user_name 
from att_persons p,USERINFO u 
where u.USERID=p.per_user_id 
and per_sch_id in (8,10) and per_status=3;

select per_status from att_persons where per_user_id=531;

select MAX(per_status) from att_persons where per_user_id=420;


select u.tea_id,p.per_title,p.per_user_name 
from USERINFO u,att_persons p 
where u.USERID=p.per_user_id 
and p.per_status = 3
and p.per_sch_id in (8,10)
order by p.per_user_name 

select * from att_persons


---考勤修改
select o.Name,a.* from Attendance a,USERINFO o ,DEPARTMENTS d
where a.att_user_id=o.USERID and o.DEFAULTDEPTID=d.DEPTID
and o.Name like '%操海棠%'
order by a.att_date desc;

select * from Attendance

update Attendance set att_confirm=5 where att_id in (162443);

